﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DotSpatial.Topology;


namespace PoemsSystem
{
    public class Database
    {
        public static SqlDataReader ReadData(String poemName)
        {
            String connString = @"Data Source=MSI\SQLEXPRESS;
                Database=master;
                AttachDbFilename=D:\SQLserver\MSSQL15.SQLEXPRESS\MSSQL\DATA\master.mdf;
                Integrated Security=True";
            String commandTextQuery = "SELECT * FROM dbo.data where title like '%" +poemName+"%'";
            SqlConnection connection = new SqlConnection(connString);
            connection.Open();
            SqlCommand cmdQuery = new SqlCommand(commandTextQuery, connection);
            SqlDataReader reader = cmdQuery.ExecuteReader();
            return reader;
        }
        public static SqlDataReader ReadGraphData()
        {
            String connString = @"Data Source=MSI\SQLEXPRESS;
                Database=master;
                AttachDbFilename=D:\SQLserver\MSSQL15.SQLEXPRESS\MSSQL\DATA\master.mdf;
                Integrated Security=True";
            String commandTextQuery = "SELECT * FROM dbo.guanzhi";
            SqlConnection connection = new SqlConnection(connString);
            connection.Open();
            SqlCommand cmdQuery = new SqlCommand(commandTextQuery, connection);
            SqlDataReader reader = cmdQuery.ExecuteReader();
            return reader;
        }

        public static SqlDataReader Readloc(String locName)
        {
            String connString= @"Data Source=MSI\SQLEXPRESS;
                Database=master;
                AttachDbFilename=D:\SQLserver\MSSQL15.SQLEXPRESS\MSSQL\DATA\master.mdf;
                Integrated Security=True";
            String commandTextQuery = "SELECT * FROM dbo.data where location like '%" + locName + "%'";
            SqlConnection connection = new SqlConnection(connString);
            connection.Open();
            SqlCommand cmdQuery = new SqlCommand(commandTextQuery, connection);
            SqlDataReader reader = cmdQuery.ExecuteReader();
            return reader;
        }

        public static SqlDataReader Readancloc(String ancLoc)
        {
            String connString = @"Data Source=MSI\SQLEXPRESS;
                Database=master;
                AttachDbFilename=D:\SQLserver\MSSQL15.SQLEXPRESS\MSSQL\DATA\master.mdf;
                Integrated Security=True";
            String commandTextQuery = "SELECT * FROM dbo.data where anc_loc like '%" + ancLoc + "%'";
            SqlConnection connection = new SqlConnection(connString);
            connection.Open();
            SqlCommand cmdQuery = new SqlCommand(commandTextQuery, connection);
            SqlDataReader reader = cmdQuery.ExecuteReader();
            return reader;
        }

        public static SqlDataReader Readtime(float time1, float time2)
        {
            String connString = @"Data Source=MSI\SQLEXPRESS;
                Database=master;
                AttachDbFilename=D:\SQLserver\MSSQL15.SQLEXPRESS\MSSQL\DATA\master.mdf;
                Integrated Security=True";
            String commandTextQuery = "select * ,rank() over (partition by time order by time)from dbo.data where time < " + time2 + "and time > " + time1;
            SqlConnection connection = new SqlConnection(connString);
            connection.Open();
            SqlCommand cmdQuery = new SqlCommand(commandTextQuery, connection);
            SqlDataReader reader = cmdQuery.ExecuteReader();
            return reader;
        }

        public static SqlDataReader Readtime1(float time1)
        {
            String connString = @"Data Source=MSI\SQLEXPRESS;
                Database=master;
                AttachDbFilename=D:\SQLserver\MSSQL15.SQLEXPRESS\MSSQL\DATA\master.mdf;
                Integrated Security=True";
            String commandTextQuery = "select * ,rank() over (partition by time order by time)from dbo.data where time > " + time1;
            SqlConnection connection = new SqlConnection(connString);
            connection.Open();
            SqlCommand cmdQuery = new SqlCommand(commandTextQuery, connection);
            SqlDataReader reader = cmdQuery.ExecuteReader();
            return reader;
        }

        public static SqlDataReader Readtime2(float time2)
        {
            String connString = @"Data Source=MSI\SQLEXPRESS;
                Database=master;
                AttachDbFilename=D:\SQLserver\MSSQL15.SQLEXPRESS\MSSQL\DATA\master.mdf;
                Integrated Security=True";
            String commandTextQuery = "select * ,rank() over (partition by time order by time)from dbo.data where time < " + time2;
            SqlConnection connection = new SqlConnection(connString);
            connection.Open();
            SqlCommand cmdQuery = new SqlCommand(commandTextQuery, connection);
            SqlDataReader reader = cmdQuery.ExecuteReader();
            return reader;
        }

        public static SqlDataReader ReadDataToDrawLine()
        {
            String connString = @"Data Source=MSI\SQLEXPRESS;
                Database=master;
                AttachDbFilename=D:\SQLserver\MSSQL15.SQLEXPRESS\MSSQL\DATA\master.mdf;
                Integrated Security=True";
            String commandTextQuery = "SELECT * FROM dbo.track";
            SqlConnection connection = new SqlConnection(connString);
            connection.Open();
            SqlCommand cmdQuery = new SqlCommand(commandTextQuery, connection);
            SqlDataReader reader = cmdQuery.ExecuteReader();
            return reader;
        }
        public static void ReadDataToDrawText(List<DrawTracks.Location> locations)
        {
            String connString_Text = @"Data Source=MSI\SQLEXPRESS;
                Database=master;
                AttachDbFilename=D:\SQLserver\MSSQL15.SQLEXPRESS\MSSQL\DATA\master.mdf;
                Integrated Security=True";
            String commandTextQuery_Text = "select l.name,c.now_name,l.lat,l.lon from dbo.location l join dbo.com_loc c on(cast(l.name as varchar)=cast(c.ori_name as varchar))";
            SqlConnection connection_Text = new SqlConnection(connString_Text);
            connection_Text.Open();
            SqlCommand cmdQuery_Text = new SqlCommand(commandTextQuery_Text, connection_Text);
            SqlDataReader reader_Text = cmdQuery_Text.ExecuteReader();
            DrawTracks.Location location = new DrawTracks.Location();
            while (reader_Text.Read())
            {
                location.anc_name = reader_Text[0].ToString();
                location.cur_name = reader_Text[1].ToString();
                location.location = new Coordinate(
                    double.Parse(reader_Text[2].ToString()),
                    double.Parse(reader_Text[3].ToString()));
                locations.Add(location);
            }
            reader_Text.Close();
        }
    }
}
